/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package gruppo12;


import java.sql.*;
import java.util.Locale;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.WebParam;

/**
 *
 * @author Michele
 */
@WebService(serviceName = "LoginService")
public class LoginService {

    

    /**
     * Web service operation
     */
    @WebMethod(operationName = "Login")
    public Boolean Login(@WebParam(name = "user") String user, @WebParam(name = "pass") String pass)  {
        //TODO write your implementation code here:
        try {
		Connection connection = new DbConnection().getDBConnection();
		Statement sql_stmt = connection.createStatement();
                String loginQuery = "SELECT * FROM test.utenti";
                ResultSet rset = sql_stmt.executeQuery (loginQuery);
                while (rset.next()) {
                    if (rset.getString(1).equals(user.toLowerCase()) && rset.getString(2).equals(pass)) return true;
                }
                connection.close();
        } catch (SQLException e) {
 
	 Logger.getLogger(AnagraficaService.class.getName()).log(Level.SEVERE, null, e);
            return false;
         }
        return false;
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "Registra")
    public Boolean Registra(@WebParam(name = "utente") String utente, @WebParam(name = "pass") String pass, @WebParam(name = "tipo") int tipo) {
       try {
		Connection connection = new DbConnection().getDBConnection();
		PreparedStatement preparedStatementInsert=null;
                String loginQuery = "INSERT INTO test.utenti (utente,pass,tipo) VALUES (?,?,?)";
               preparedStatementInsert = connection.prepareStatement(loginQuery);
			preparedStatementInsert.setString(1, utente.toLowerCase());
			preparedStatementInsert.setString(2, pass);
			preparedStatementInsert.setInt(3, tipo);
                        Boolean ans = new DbConnection().controllo(utente.toLowerCase(),"utente", "test.utenti");
                        int rs=0;
                        if(ans)
                         rs = preparedStatementInsert.executeUpdate();
                        else return false;
			
 
			
 
			connection.commit();
                        connection.close();
 
			if (rs == 1 ){
                            System.out.println("Done!");
                            return true;
                        }
                         
                        return false;
                
        } catch (SQLException e) {
 
	 Logger.getLogger(AnagraficaService.class.getName()).log(Level.SEVERE, null, e);
            return false;
         }
       
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "Reset")
    public Boolean Reset() {Connection dbConnection = null;
		PreparedStatement preparedStatementInsert = null;
             
 
		String query = "DELETE *"
				+ "FROM test.utenti";
     
     
     try {
			dbConnection = new DbConnection().getDBConnection();
 
			dbConnection.setAutoCommit(false);
                        preparedStatementInsert = dbConnection.prepareStatement(query);
			
                        
                     
		
                  
                        int rs = preparedStatementInsert.executeUpdate();
			
 
			
 
			dbConnection.commit();
 
			if (rs == 1 ){
                            System.out.println("Done!");
                            return true;
                        }
                         
                        return false;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
            try {
                dbConnection.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
            }
                        return false;
 
		} finally {
 
			if (preparedStatementInsert != null) {
                try {
                    preparedStatementInsert.close();
                } catch (SQLException ex) {
                    Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
                }
			}
 
			
 
			if (dbConnection != null) {
                try {
                    dbConnection.close();
                } catch (SQLException ex) {
                    Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
                }
			}
 
		}
        //TODO write your implementation code here:
        
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "controlloAccediRegistra")
    public Boolean controlloAccedoRegistro(@WebParam(name = "utente") String utente, @WebParam(name = "pass") String pass) {
        try {   
		Connection connection = new DbConnection().getDBConnection();
		Statement sql_stmt = connection.createStatement();
                String loginQuery = "SELECT * FROM test.utenti WHERE utente='"+utente.toLowerCase()+"' AND pass='"+pass+"'";
                ResultSet rset = sql_stmt.executeQuery (loginQuery);
                if (rset==null)return false;
                while (rset.next()) {
                    if (rset.getInt(3)==0 ) return true;
                   
                }
                
                connection.close();
        } catch (SQLException e) {

	 Logger.getLogger(AnagraficaService.class.getName()).log(Level.SEVERE, null, e);
            return false;
         }
        return false;
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "VisualizzaUtenti")
    public String[][] VisualizzaUtenti() {
        Connection dbConnection = null;
		java.sql.Statement statement = null;
             
 
		String insertTableSQL = "SELECT * "
				+ "FROM test.utenti";
                                  
			
 
		
 
 
		try {
			dbConnection = new DbConnection().getDBConnection();
 
			dbConnection.setAutoCommit(false);
 
			statement = dbConnection.createStatement(1004, 1008);
		
                        
                   
		 
                    
                       java.sql.ResultSet rs = statement.executeQuery(insertTableSQL);
		      
                       String[][] results=null;
                       
                       int rows=0;
                       while(rs.next())//scorre all'ultimo record del select
                       {rows++;}
                       results= new String[rows][];    
                       rs.beforeFirst();                      //ritorna il cursore all'inizio
                       int i=0;
                       while (rs.next()){
                           String[] record = new String[3];
                           record[0]=rs.getString(1);
                           record[1]=rs.getString(2);
                           switch(rs.getInt(3)){
                               case 1: record[2]="ALL";
                                   break;
                               case 2:record[2]="PROD";
                                   break;
                               case 3:record[2]="COMM";
                                   break;
                               case 0:record[2]="ADMIN";
                                   
                           }
                           results[i]=record ;
                           i++;
                       }
                       int j;
                       	for(int k=1;k<results.length;k++){
                            String[] temp=results[k];
                            j=k-1;
                            while((j>=0)&&(results[j][0].charAt(0) >temp[0].charAt(0)))
                                	{
				results[j+1]=results[j];
				j--;
                            
			}
                            results[j+1]=temp;
                        }		
                       
			dbConnection.commit();
 
			return results;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
            try {
                dbConnection.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
            }
                        return null;
 
		} finally {
 
			
 
			
 
			if (dbConnection != null) {
                try {
                    dbConnection.close();
                } catch (SQLException ex) {
                    Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
                }
			}
 
		}
 
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "cancellaUtente")
    public Boolean cancellaUtente(@WebParam(name = "UserName") String UserName) {
       Connection dbConnection = null;
		PreparedStatement preparedStatementInsert = null;
             
              
		String query = "DELETE "
				+ "FROM test.utenti WHERE utente = '"+UserName+"'";
     
     
     try {
			dbConnection = new DbConnection().getDBConnection();
 
			dbConnection.setAutoCommit(false);
                        preparedStatementInsert = dbConnection.prepareStatement(query);
			
                        
                     
		
                  
                        int rs = preparedStatementInsert.executeUpdate();
			
 
			
 
			dbConnection.commit();
 
			if (rs == 1 ){
                            System.out.println("Done!");
                            return true;
                        }
                         
                        return false;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
            try {
                dbConnection.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
            }
                        return false;
 
		} finally {
 
			if (preparedStatementInsert != null) {
                try {
                    preparedStatementInsert.close();
                } catch (SQLException ex) {
                    Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
                }
			}
 
			
 
			if (dbConnection != null) {
                try {
                    dbConnection.close();
                } catch (SQLException ex) {
                    Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
                }
			}
 
		}
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "RicercaUtente")
    public String[][] RicercaUtente(@WebParam(name = "colonna") String colonna,@WebParam(name = "filter") String filter) {
       Connection dbConnection = null;
		java.sql.Statement statement = null;
               String insertTableSQL = "SELECT * "
				+ "FROM test.utenti WHERE "+colonna+" LIKE '"+ filter.toLowerCase() +"%'";                          
			
 
		
 
 
		try {
			dbConnection = new DbConnection().getDBConnection();
 
			dbConnection.setAutoCommit(false);
 
			statement = dbConnection.createStatement(1004, 1008);
		
                        
                   
		 
                    
                       java.sql.ResultSet rs = statement.executeQuery(insertTableSQL);
		      
                       String[][] results=null;
                       
                       int rows=0;
                       while(rs.next())//scorre all'ultimo record del select
                       {rows++;}
                       results= new String[rows][];    
                       rs.beforeFirst();                      //ritorna il cursore all'inizio
                       int i=0;
                       while (rs.next()){
                           String[] record = new String[3];
                           record[0]=rs.getString(1);
                           record[1]=rs.getString(2);
                           switch(rs.getInt(3)){
                               case 1: record[2]="ALL";
                                   break;
                               case 2:record[2]="PROD";
                                   break;
                               case 3:record[2]="COMM";
                                   break;}
                           results[i]=record ;
                           //ordine alfabetico con insertion sort
                           
                               
                           i++;
                       }
                int j;
                       	for(int k=1;k<results.length;k++){
                            String[] temp=results[k];
                            j=k-1;
                            while((j>=0)&&(results[j][0].charAt(0) >temp[0].charAt(0)))
                                	{
				results[j+1]=results[j];
				j--;
                            
			}
                            results[j+1]=temp;
                        }	
 
			
 
			dbConnection.commit();
 
			return results;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
            try {
                dbConnection.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
            }
                        return null;
 
		} finally {
 
			
 
			
 
			if (dbConnection != null) {
                try {
                    dbConnection.close();
                } catch (SQLException ex) {
                    Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
                }
			}
 
		}
 
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "controlloPresenzaAdmin")
    public Boolean controlloPresenzaAdmin() {
        Connection dbConnection = null;
		java.sql.Statement statement = null;
             
 
		String insertTableSQL = "SELECT * "
				+ "FROM test.utenti WHERE tipo = 0";
                                  
			
 
		
 
 
		try {
			dbConnection = new DbConnection().getDBConnection();
 
			dbConnection.setAutoCommit(false);
 
			statement = dbConnection.createStatement(1004, 1008);
		
                        
                   
		 
                    
                       java.sql.ResultSet rs = statement.executeQuery(insertTableSQL);
		      
                  		
                       if(!rs.next())return false;
			dbConnection.commit();
                       
			
                        
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
            try {
                dbConnection.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
            }
                        
 
		} 
                
                finally {
 
			
 
			
 
			if (dbConnection != null) {
                try {
                    dbConnection.close();
                } catch (SQLException ex) {
                    Logger.getLogger(LoginService.class.getName()).log(Level.SEVERE, null, ex);
                }
			}
 
		}
                return true;
 
    }

   
    
}

